package com.lagou.dao.Impl.Impl;

import com.lagou.dao.Impl.CourseContentDao;
import com.lagou.pojo.Course;
import com.lagou.pojo.Course_Lesson;
import com.lagou.pojo.Course_Section;
import com.lagou.utils.DateUtils;
import com.lagou.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;


/*
*
* 课程内容管理实现类
*
*
* */
public class CourseContentDaoImpl implements CourseContentDao {

    //根据课程ID查询课程相关信息
    @Override
    public List<Course_Section> findSectionAndLessonByCourseId(int courseId) {

        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner (DruidUtils.getDataSource ());

            //2.编写sql
            String sql = "SELECT\n" +
                    "id,\n" +
                    "course_id,\n" +
                    "section_name,\n" +
                    "order_num,\n" +
                    "STATUS\n" +
                    "FROM course_section WHERE course_id = ?;";
            //3.执行查询
            List<Course_Section> sectionList = qr.query (sql, new BeanListHandler<Course_Section> (Course_Section.class), courseId);
            //4.根据章节id查询课时信息
            for (Course_Section section : sectionList) {

                //调用方法
                List<Course_Lesson> lessonList = findLessonBySectionId (section.getId ());

                //将课时数据封装到章节对象中
                section.setLessonsList (lessonList);
            }
            return sectionList;
        } catch (SQLException e) {
            e.printStackTrace ();
            return null;
        }
    }


    //根据章节id查询章节相关的课时信息
    @Override
    public List<Course_Lesson> findLessonBySectionId(int sectionId) {

        try {
            QueryRunner qr = new QueryRunner (DruidUtils.getDataSource ());

            String sql = "SELECT\n" +
                    "id,\n" +
                    "course_id,\n" +
                    "section_id,\n" +
                    "theme,\n" +
                    "duration,\n" +
                    "is_free,\n" +
                    "order_num,\n" +
                    "STATUS\n" +
                    "FROM course_lesson WHERE section_id = ?";

            List<Course_Lesson> lessonList = qr.query (sql, new BeanListHandler<Course_Lesson> (Course_Lesson.class), sectionId);
            return lessonList;
        } catch (SQLException e) {
            e.printStackTrace ();
            return null;
        }
    }


    //根据课程id 回显课程信息
    @Override
    public Course findCourseByCourseId(int courseId) {

        try {
            QueryRunner qr = new QueryRunner (DruidUtils.getDataSource ());
            String sql = "SELECT id,course_name FROM course WHERE id = ?;";
            Course course = qr.query (sql, new BeanHandler<Course> (Course.class), courseId);
            return course;
        } catch (Exception e) {
            e.printStackTrace ();
            return null;
        }
    }


    //保存章节信息
    @Override
    public int saveSection(Course_Section section) {

        try {
            QueryRunner qr = new QueryRunner (DruidUtils.getDataSource ());

            String sql = "INSERT INTO course_section(\n" +
                    "course_id,\n" +
                    "section_name,\n" +
                    "description,\n" +
                    "order_num,\n" +
                    "STATUS,\n" +
                    "create_time,\n" +
                    "update_time)VALUES(?,?,?,?,?,?,?)";
            Object[] param = {section.getCourse_id (),section.getSection_name (),section.getDescription (),section.getOrder_num (),
            section.getStatus (),section.getCreate_time (),section.getUpdate_time ()};
            int row = qr.update (sql, param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace ();
            return 0;
        }
    }

    //修改章节信息
    @Override
    public int updateSection(Course_Section section) {

        try {
            QueryRunner qr = new QueryRunner (DruidUtils.getDataSource ());

            String sql = "UPDATE course_section SET\n" +
                    "section_name = ?,\n" +
                    "description = ?,\n" +
                    "order_num = ?,\n" +
                    "update_time =? WHERE id = ?";

            Object[] param = {section.getSection_name (),section.getDescription (),section.getOrder_num (),section.getUpdate_time (),
            section.getId ()};

            int row = qr.update (sql, param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace ();
            return 0;
        }
    }

    //修改章节状态
    @Override
    public int updateSectionStatus(int id, int status) {

        try {
            QueryRunner qr = new QueryRunner (DruidUtils.getDataSource ());

            String sql = "update course_section SET STATUS = ?,update_time = ? WHERE id = ?";

            Object[] param = {status,DateUtils.getDateFormart (),id};

            int row = qr.update (sql, param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace ();
            return 0;
        }
    }

    //保存课时信息
    @Override
    public int saveLesson(Course_Lesson lesson) {

        try {
            QueryRunner qr = new QueryRunner (DruidUtils.getDataSource ());

            String sql = "INSERT INTO course_lesson(\n" +
                    "course_id,\n" +
                    "section_id,\n" +
                    "theme,\n" +
                    "duration,\n" +
                    "is_free,\n" +
                    "order_num,\n" +
                    "status,\n" +
                    "create_time,\n" +
                    "update_time\n" +
                    ") VALUES (?,?,?,?,?,?,?,?,?)";

            Object[] param = {lesson.getCourse_id (),lesson.getSection_id (),lesson.getTheme (),
                    lesson.getDuration (),lesson.getIs_free (),lesson.getOrderNum (),lesson.getStatus (),lesson.getCreate_time (),
                    lesson.getUpdate_time ()};

            int row = qr.update (sql, param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace ();
            return 0;
        }
    }

    //修改课时信息
    @Override
    public int updateLesson(Course_Lesson lesson) {

        try {
            QueryRunner qr = new QueryRunner (DruidUtils.getDataSource ());

            String sql = "UPDATE course_lesson SET\n" +
                    "theme = ?,\n" +
                    "duration =?,\n" +
                    "is_free =?,\n" +
                    "order_num =?,\n" +
                    "update_time =?\n" +
                    "WHERE id =?";
            Object[] param = {lesson.getTheme (),lesson.getDuration (),lesson.getIs_free (),
                    lesson.getOrderNum (),lesson.getUpdate_time (),lesson.getId ()};
            int row = qr.update (sql, param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace ();
            return 0;
        }

    }

    //修改课时状态
    @Override
    public int updateLessonStatus(int id, int status) {

        try {
            QueryRunner qr = new QueryRunner (DruidUtils.getDataSource ());

            String sql = "update course_lesson SET STATUS = ?,update_time = ? WHERE id = ?";

            Object[] param = {status,DateUtils.getDateFormart (),id};

            int row = qr.update (sql, param);

            return row;
        } catch (SQLException e) {
            e.printStackTrace ();
            return 0;
        }

    }
}
